import pandas as pd
import numpy as np
import MySQLdb
import re
import sys
import time


conn =  MySQLdb.connect("localhost", "root", "zhaoming832002", "proxy", charset='utf8' )
cursor = conn.cursor()
sql= "SELECT a.*,b.* FROM proxy.fanghouseinfo as a, proxy.fangcatalog as b where a.idcatalog=b.id"
sql= "SELECT a.*,b.* FROM proxy.fanghouseinfo as a, proxy.fangcatalog as b where a.idcatalog=b.id and b.cityname='长沙'"
cursor.execute(sql)

c=[x[0] for x in cursor.description]
rows=cursor.fetchall()
df = pd.DataFrame(list(rows), columns=[x[0] for x in cursor.description])
df = df.drop(["idCatalog", "id", "urlNewHouse", "urlSencondHandHouse", "urlRentalhousing"], 1)
#df[2:4]
columnReindex=["collecttime", "ProvinceName", "CityName", "type", "name", "address", "housetype", "characteristic", "telephone", "price"]
#df.rename(columns = columnReindex)
df=df[columnReindex]
dfOrign=df.copy()
#df["houseHomeNum"] = df["housetype"].str.split('－')
conditions =[df['type'] == 0,df['type'] == 1,df['type'] ==2 ]
veSplit=[df["housetype"].str.split('－',expand=True), df["housetype"].str.split('|',expand=True), df["housetype"].str.split('|',expand=True)]

choices=[]
for i in range(2):
    choices.append([veSplit[0][i].str.strip(), veSplit[1][i].str.strip(), veSplit[2][i + 1].str.strip()])
df["houseHomeNum"] = np.select(conditions, choices[0], default='')
df["houseSize"] = np.select(conditions, choices[1], default='')
df=df[df["type"]==1]


df["houseHomeNum"]=df["houseHomeNum"].str.replace("居", "室")


vMatch = df["houseHomeNum"].str.match("\d室|\d厅")
df = df.drop(index=  [i for i in vMatch.index if vMatch[i] == False])
df["houseArea"]= veSplit[1][1].str.strip()
df["houseHeight"]= veSplit[1][2].str.strip()


vMatch = df["price"].apply(lambda x: list(filter(None,x.split(" "))))
df["priceTotal"] =vMatch.apply(lambda x: x[0])
df["priceAve"] =vMatch.apply(lambda x: x[1])


vMatch = df["address"].apply(lambda x: list(filter(None,re.split(" |-", x))))

df["houseName"] =vMatch.apply(lambda x:x[0] if len(x)>0 else "")
df["houseRegion"] =vMatch.apply(lambda x: x[1] if len(x)>1 else "")
df["houseAddress"] =vMatch.apply(lambda x: x[2] if len(x)>2 else "")

#'高层（共33层）'
df["houseHeight"]=df["houseHeight"].str.replace("（", "[")
df["houseHeight"]=df["houseHeight"].str.replace("）", "]")
df["dateTime"] = df["collecttime"].apply(lambda x: time.strftime("%Y-%m-%d",  time.gmtime(x)))

def gethouseTolHeight(x):
    if x!="" and x!=None:
        s = re.search("\[[\s\S]*\]", x)
        if s == None:
            return  ""
        return s.group()[2:-2]
    return ""
    #return s[2:-2] if len(s) > 2 else ""


def gethouseLevel(x):
    if x!="" and x!=None:
        s = re.search("^[\s\S]*\[", x)
        if s == None:
            return ""
        return s.group()[:-1]
    return ""


df["houseTolHeight"] = df["houseHeight"].apply(gethouseTolHeight)
df["houseLevel"] = df["houseHeight"].apply(gethouseLevel )
df = df[df["houseTolHeight"]!=""]

df["houseTolHeight"]=df["houseTolHeight"].astype('int')

dfnew = df
dfnew = dfnew.drop(["type", "name", "address", "housetype", "telephone", "price","characteristic", "houseHeight", "houseSize"], 1)

dfnew["priceAve"] = dfnew["priceAve"] .str.replace("元/㎡", "")
vMatch = dfnew["priceAve"].str.match("^\d+$")
dfnew = dfnew.drop(index=  [inx for inx in vMatch.index if vMatch[inx] == False])
dfnew["priceAve"]=dfnew["priceAve"].astype('float')

dfnew["priceTotal"] = dfnew["priceTotal"] .str.replace("万", "")
vMatch = dfnew["priceTotal"].str.match("^\d+$")
dfnew = dfnew.drop(index=  [inx for inx in vMatch.index if vMatch[inx] == False])
dfnew["priceTotal"]=dfnew["priceTotal"].astype('float')
dfnew["priceTotal"]=dfnew["priceTotal"]*10000

dfnew.reset_index(inplace=True,drop=True)


columnReindex=["dateTime", "ProvinceName", "CityName", "houseRegion", "houseName", "houseAddress", "houseHomeNum", "houseArea", "houseTolHeight","houseLevel","priceTotal", "priceAve"]
dfnew=dfnew[columnReindex]
dfnew.to_csv("d:\\2手房.csv")
dfnew=dfnew[dfnew["houseTolHeight"]>7]
#c = dfnew.groupby(["ProvinceName", "CityName", "region", "住宅类型"])["price"].mean()

fnewGroup=dfnew.groupby(["ProvinceName", "CityName",  "houseRegion", "houseName","dateTime"])["priceAve"].mean()
fnewGroup.to_csv("d:\\2.csv")
